﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace IceCS.GoodsSale.DAL
{
    /// <summary>
    /// DBHelper类
    /// </summary>
    public class DBHelper
    {
        //定义数据库连接对象
        public static SqlConnection Conn = new SqlConnection();

        /// <summary>
        /// 获取数据的方法，返回DataTable对象，参数为一个select语句
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sqlStr)
        {
            try
            {
                Conn.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, Conn);
                SqlDataAdapter dapt = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dapt.Fill(dt);
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Conn.Close();
            }
        }

        /// <summary>
        /// 获取数据的重载方法，返回DataTable对象，参数为一个参数化的select语句和参数对象数组
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sqlStr, SqlParameter[] param)
        {
            try
            {
                Conn.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, Conn);
                cmd.Parameters.AddRange(param);
                SqlDataAdapter dapt = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dapt.Fill(dt);
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Conn.Close();
            }
        }

        /// <summary>
        /// 执行更新的方法，返回一个布尔值，参数为一个insert|update|delete语句
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public bool ExcuteCommand(string sqlStr)
        {
            try
            {
                Conn.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, Conn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                Conn.Close();
            }
        }

        /// <summary>
        /// 执行更新的重载方法，返回一个布尔值，参数为一个参数化的insert|update|delete语句和参数对象数组
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool ExcuteCommand(string sqlStr, SqlParameter[] param)
        {
            try
            {
                Conn.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, Conn);
                cmd.Parameters.AddRange(param);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                Conn.Close();
            }
        }
    }
}
